package com.ezops.controller;

import static com.ezops.util.ReportDataHelper.REPORTS_FOLDER_PATH;
import static com.ezops.util.ReportDataHelper.REPORT_PRODUCT_BY_CP_CSV;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.nio.ByteBuffer;
import java.nio.channels.FileChannel;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVRecord;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.ezops.dto.CashBalanceCurrencyAmountModel;
import com.ezops.dto.CashBalanceReportChartModel;
import com.ezops.dto.CashBalancesChartModel;
import com.ezops.dto.CashByClearer;
import com.ezops.dto.CashByClearerHeaderAndData;
import com.ezops.dto.CashByClearerSeriesCurrencyData;
import com.ezops.dto.CashByClearerSeriesDrildownData;
import com.ezops.dto.CashClearerResultData;
import com.ezops.dto.CashUpcomingPayRecieveModel;
import com.ezops.dto.DashboradPositionChartsModel;
import com.ezops.dto.FinalDepotProjectionModel;
import com.ezops.dto.LifeCycleEventModel;
import com.ezops.dto.MasterAggrementAgreementCountModel;
import com.ezops.dto.MasterAggrementReportChartModel;
import com.ezops.dto.MasterAgreementChartModel;
import com.ezops.dto.MasterAgreementFinalModel;
import com.ezops.dto.RiskCashFlowAnalysis;
import com.ezops.dto.RiskTimeHorizonModel;
import com.ezops.exception.EzopsUIException;
import com.ezops.filter.ReportCriteria;
import com.ezops.filter.ReportCriteria.Filter;
import com.ezops.filter.ReportCriteriaImpl;
import com.ezops.gateway.DefaultReportCallBackHandler;
import com.ezops.gateway.IEzopsRemoteGateway;
import com.ezops.service.IReportLocatorService;
import com.ezops.util.EzopsReportsProvider;
import com.ezops.util.Report;
import com.ezops.util.Report.Row;
import com.ezops.util.ReportDataHelper;
import com.ezops.util.ReportMapper;

/**
 * Controller for serving requests related to dashboard reports.
 * 
 * @author vgaurav
 * 
 */
@Controller
@RequestMapping("/getReports")
public class ReportController {

	protected static Logger logger = LoggerFactory
			.getLogger(ReportController.class);

	DateFormat format = new SimpleDateFormat("yyyyMMdd");
	DateFormat format1 = new SimpleDateFormat("MM/dd/yyyy");
	DateFormat format2 = new SimpleDateFormat("dd-MMM");

	@Autowired
	private IEzopsRemoteGateway remoteGateway;

	@Autowired
	private IReportLocatorService reportLocator;

	@Autowired
	private IReportLocatorService reportLocatorService;

	private ReportCriteria createCriteria() {
		ReportCriteria criteria = ReportCriteriaImpl.createInstance();
		criteria.addFilter(Filter.REPORT_NAME, REPORT_PRODUCT_BY_CP_CSV)
				.addFilter(Filter.REPORT_DISPLAY_NAME, "Procuct_by_CP");

		return criteria;
	}

	/**
	 * Get double representation of the amount, of unparaable return 0.
	 * 
	 * @param amount
	 * @return
	 */
	private Double parseDouble(String amount) {
		Double parsedValue = 0.0;
		try {
			if(amount != ""){
				// The value could be in "" , remove quote before parsing.
				amount = amount.replaceAll("\"", "");
				parsedValue = Double.parseDouble(amount);
			}
			else{
				return parsedValue;
			}
		} catch (Exception e) {
			logger.info("Unable to parse " + amount
					+ " as double value. Skipping this record");

		}

		return parsedValue;
	}

	private void checkForRefresh(boolean refreshed, ReportCriteria criteria) {
		// If refresh then get the report from OT.
		if (refreshed) {

			DefaultReportCallBackHandler handler = new DefaultReportCallBackHandler(
					criteria);
			remoteGateway.sendRemoteRequest(criteria.build(), handler);
			// Temp solution of holding the request
			handler.isReponseReceived();
		}
	}

	/**
	 * Parse Master Agreement Pie Chart
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getpositionbyproductpiechart", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<MasterAgreementChartModel> getPositionByProductPiehart(
			@RequestParam(value = "refresh", required = false, defaultValue = "false") boolean refreshed)
			throws EzopsUIException {

		ReportCriteria criteria = createCriteria();
		checkForRefresh(refreshed, criteria);

		Report report = null;
		Map<String, Integer> responseMesage = new HashMap<String, Integer>();
		try {
			report = new Report(new File(reportLocatorService.locate(criteria)));
			Iterator<Row> iterator = report.iterator();
			while (iterator.hasNext()) {
				Row row = iterator.next();
				if (responseMesage.containsKey(row.getValue(1))) {
					responseMesage.put(row.getValue(1),
							responseMesage.get(row.getValue(1)) + 1);

				} else {
					responseMesage.put(row.getValue(1), 1);
				}
			}
			List<MasterAgreementChartModel> chartList = MasterAgreementChartModel
					.adaptTo(responseMesage);
			return chartList;
		} catch (Exception e) {
			logger.error("Unable to Parse Position By CounterParty CSV "
					+ e.getMessage());
			throw new EzopsUIException(
					"Error ocurred while showing position by currency chart");
		}
	}

	/**
	 * Parse Master Agreement Pie Chart
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getpositionbycppiechart", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<MasterAgreementChartModel> getPositionByCPPiehart(
			@RequestParam(value = "refresh", required = false, defaultValue = "false") boolean refreshed)
			throws EzopsUIException {

		ReportCriteria criteria = createCriteria();
		checkForRefresh(refreshed, criteria);

		Report report = null;
		Map<String, Integer> responseMesage = new HashMap<String, Integer>();
		try {
			report = new Report(new File(reportLocatorService.locate(criteria)));
			Iterator<Row> iterator = report.iterator();
			while (iterator.hasNext()) {
				Row row = iterator.next();
				if (responseMesage.containsKey(row.getValue(2))) {
					responseMesage.put(row.getValue(2),
							responseMesage.get(row.getValue(2)) + 1);

				} else {
					responseMesage.put(row.getValue(2), 1);
				}
			}
			List<MasterAgreementChartModel> chartList = MasterAgreementChartModel
					.adaptTo(responseMesage);
			return chartList;
		} catch (Exception e) {
			logger.error("Unable to Parse Position By CounterParty CSV "
					+ e.getMessage());
			throw new EzopsUIException(
					"Error ocurred while showing position by currency chart");
		}
	}

	/**
	 * Parse Master Agreement Pie Chart
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getpositionbycurrencypiechart", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<MasterAgreementChartModel> getPositionByCurrencyPiehart(
			@RequestParam(value = "refresh", required = false, defaultValue = "false") boolean refreshed)
			throws EzopsUIException {

		ReportCriteria criteria = createCriteria();
		checkForRefresh(refreshed, criteria);

		Report report = null;
		Map<String, Integer> responseMesage = new HashMap<String, Integer>();
		try {
			report = new Report(new File(reportLocatorService.locate(criteria)));
			Iterator<Row> iterator = report.iterator();
			while (iterator.hasNext()) {
				Row row = iterator.next();
				if (responseMesage.containsKey(row.getValue(3))) {
					responseMesage.put(row.getValue(3),
							responseMesage.get(row.getValue(3)) + 1);

				} else {
					responseMesage.put(row.getValue(3), 1);
				}
			}
			List<MasterAgreementChartModel> chartList = MasterAgreementChartModel
					.adaptTo(responseMesage);
			return chartList;
		} catch (Exception e) {
			logger.error("Unable to Parse Position By CounterParty CSV "
					+ e.getMessage());
			throw new EzopsUIException(
					"Error ocurred while showing position by currency chart");
		}
	}

	/**
	 * Parse CSV for position charts and send to display modal window in
	 * position charts
	 * 
	 * @return List<DashboradPositionChartsModel>
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/parsecsvforpositioncharts", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<DashboradPositionChartsModel> parseCsvForPositionCharts()
			throws EzopsUIException {

		Report report = null;
		// List<DashboradPositionChartsModel> positionChartsList = new
		// ArrayList<DashboradPositionChartsModel>();
		try {
			logger.debug("Parsing Position By CounterParty CSV");
			report = new Report(new File(
					reportLocatorService.locate(createCriteria())));
			return new ReportMapper().map(DashboradPositionChartsModel.class,
					report);
		} catch (Exception e) {
			logger.error("Unable to Parse Position By CounterParty CSV "
					+ e.getMessage());
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for postion charts");
		}
	}

	/**
	 * Report download in excel or PDF format
	 * 
	 * @param downloadFileFormat
	 * @param response
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/downloadreportinxlsorpdf", method = RequestMethod.GET)
	public void downloadReportInExcelOrPdf(
			@RequestParam("downloadFileFormat") String downloadFileFormat,
			HttpServletResponse response) throws EzopsUIException {
		String fileName = null;
		if (downloadFileFormat.equalsIgnoreCase("PositionbyCounterpartyExcel")) {
			response.setContentType("text/csv");
			fileName = REPORTS_FOLDER_PATH + "PositionbyCounterparty.csv";
			response.setHeader("Content-Disposition",
					"attachment; filename=PositionbyCounterparty.csv");
		} else if (downloadFileFormat.equalsIgnoreCase("PositionbyCounterpartyPdf")) {
			response.setContentType("application/pdf");
			fileName = REPORTS_FOLDER_PATH + "PositionbyCounterparty.pdf";
			response.setHeader("Content-Disposition",
					"attachment; filename=PositionbyCounterparty.pdf");
		} else if (downloadFileFormat.equalsIgnoreCase("MasterAgreementReportExcel")) {
			response.setContentType("application/pdf");
			fileName = REPORTS_FOLDER_PATH + "MasterAgreementFinal_SS.xlsx";
			response.setHeader("Content-Disposition",
					"attachment; filename=MasterAgreementFinal_SS.xlsx");
		} else if (downloadFileFormat.equalsIgnoreCase("MasterAgreementReportPdf")) {
			response.setContentType("application/pdf");
			fileName = REPORTS_FOLDER_PATH + "MasterAgreementFinal_SS.pdf";
			response.setHeader("Content-Disposition",
					"attachment; filename=MasterAgreementFinal_SS.pdf");
		} else if (downloadFileFormat.equalsIgnoreCase("RiskCashFlowAnalysisExcel")) {
			response.setContentType("application/pdf");
			fileName = REPORTS_FOLDER_PATH + "Risk_CashFlow Analysis_SS.xlsx";
			response.setHeader("Content-Disposition",
					"attachment; filename=Risk_CashFlow Analysis_SS.xlsx");
		} else if (downloadFileFormat.equalsIgnoreCase("RiskCashFlowAnalysisPdf")) {
			response.setContentType("application/pdf");
			fileName = REPORTS_FOLDER_PATH + "Risk_CashFlow Analysis_SS.pdf";
			response.setHeader("Content-Disposition",
					"attachment; filename=Risk_CashFlow Analysis_SS.pdf");
		}
		ByteBuffer buff = null;
		try (FileInputStream input = new FileInputStream(fileName);
				FileChannel fchan = input.getChannel();) {

			long fsize = fchan.size();
			buff = ByteBuffer.allocate((int) fsize);
			fchan.read(buff);
			buff.rewind();
			byte[] data = buff.array();

			ServletOutputStream out = response.getOutputStream();
			out.write(data);

		} catch (Exception e) {
			logger.error("There is some issue in file Download"
					+ e.getMessage());
			throw new EzopsUIException("Error Ocuured while downloading report");
		} finally {
			buff.clear();
		}
	}

	/**
	 * Parse excel to display cash balance report data into grid
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getcashbalancesgrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<CashBalancesChartModel> getCashBalancesGrid() throws EzopsUIException {

		Report report = null;
		// String fs = System.getProperty("file.seperator");
		// List<CashBalancesChartModel> cashBalancesGridList = new
		// ArrayList<CashBalancesChartModel>();
		try {
			logger.debug("Parsing Cash Balances Report ");
			String fs = System.getProperty("file.separator");
			report = new Report();
			report.loadXls(new FileInputStream(new File(REPORTS_FOLDER_PATH)
					+ fs + "CashBalances.xlsx"), "Sheet");
			return new ReportMapper().map(CashBalancesChartModel.class, report);
		} catch (Exception e) {
			logger.error("Unable to Parse Cash Balances Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for Cash Balances Report ");
		}
		// System.out.println(cashBalancesGridList.toString());
		// return cashBalancesGridList;
	}

	/**
	 * Parse cash balance report to display data into chart
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getcashbalanceschart", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	CashBalanceReportChartModel getCashBalancesChart() throws EzopsUIException {

		Report report = null;
		try {
			logger.debug("Parsing Cash Balances Report ");
			report = new Report();
			String fs = System.getProperty("file.separator");
			report.loadXls(new FileInputStream(new File(REPORTS_FOLDER_PATH)
					+ fs + "CashBalances.xlsx"), "Sheet");
			Iterator<Row> iterator = report.iterator();

			Map<DateCurrencyKey, Double> intermediateMap = new HashMap<>();
			while (iterator.hasNext()) {
				Row row = iterator.next();
				// String valueDate = row.getValue(0);
				// CashBalanceChartModel cc = new CashBalanceChartModel();
				// CURR , DATE , AMPUNT

				String currency = row.getValue(3);
				String date = row.getValue(0);
				Date date1 = format.parse(date);
				String dateStr = format2.format(date1);
				String amt = row.getValue(6);

				DateCurrencyKey key = new DateCurrencyKey(currency, dateStr);

				if (intermediateMap.containsKey(key)) {
					Double oldValue = intermediateMap.get(key);
					try {
						intermediateMap.put(key,
								oldValue + Double.parseDouble(amt));
					} catch (Exception e) {
						// eat the exception
					}
				} else {
					try {
						intermediateMap.put(key,
								Double.parseDouble(amt.replaceAll(",", "")));
					} catch (Exception e) {
						intermediateMap.put(key, 0.0);

					}
				}

			}
			List<String> allDates = extractDistinctDates(intermediateMap);
			Map<String, List<Double>> currencyMap = extractCurrency(intermediateMap);
			generateCashBalacneReportData(allDates, currencyMap,
					intermediateMap);
			// -----------------------------//
			// Map<String, List<CurrencyAmtValue>> cashBalanceMap =
			// convertToUIModel(intermediateMap);

			CashBalanceReportChartModel chartData = new CashBalanceReportChartModel();

			chartData.setValueDates(allDates);
			chartData
					.setCashBalanceCurrencyAmountModel(CashBalanceCurrencyAmountModel
							.adaptTo(currencyMap));
			return chartData;
		} catch (Exception e) {
			logger.error("Unable to Parse Cash Balances Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching bar chart data for Cash Balances Report ");
		}
	}

	/**
	 * Parse excel to display cash balance report data into grid
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getcashdailychangegrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<CashBalancesChartModel> getCashDailyChangeGrid()
			throws EzopsUIException {

		Report report = null;
		// String fs = System.getProperty("file.seperator");
		// List<CashBalancesChartModel> cashBalancesGridList = new
		// ArrayList<CashBalancesChartModel>();
		try {
			logger.debug("Parsing Cash Balances Report ");
			String fs = System.getProperty("file.separator");
			report = new Report();
			report.loadXls(new FileInputStream(new File(REPORTS_FOLDER_PATH)
					+ fs + "Cash_Daily Change_SS.xlsx"), "Sheet");
			return new ReportMapper().map(CashBalancesChartModel.class, report);
		} catch (Exception e) {
			logger.error("Unable to Parse Cash Balances Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for Cash Balances Report ");
		}
		// System.out.println(cashBalancesGridList.toString());
		// return cashBalancesGridList;
	}

	/**
	 * Parse cash balance report to display data into chart
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getcashdailychangechart", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	CashBalanceReportChartModel getCashDailyChangeChart()
			throws EzopsUIException {

		Report report = null;
		try {
			logger.debug("Parsing Cash Daily Change Report ");
			report = new Report();
			String fs = System.getProperty("file.separator");
			report.loadXls(new FileInputStream(new File(REPORTS_FOLDER_PATH)
					+ fs + "Cash_Daily Change_SS.xlsx"), "Sheet");
			Iterator<Row> iterator = report.iterator();

			Map<DateCurrencyKey, Double> intermediateMap = new HashMap<>();
			while (iterator.hasNext()) {
				Row row = iterator.next();
				// String valueDate = row.getValue(0);
				// CashBalanceChartModel cc = new CashBalanceChartModel();
				// CURR , DATE , AMPUNT

				String currency = row.getValue(3);
				String date = row.getValue(0);
				Date date1 = format.parse(date);
				String dateStr = format2.format(date1);
				String amt = row.getValue(6);

				DateCurrencyKey key = new DateCurrencyKey(currency, dateStr);

				if (intermediateMap.containsKey(key)) {
					Double oldValue = intermediateMap.get(key);
					try {
						intermediateMap.put(key,
								oldValue + Double.parseDouble(amt));
					} catch (Exception e) {
						// eat the exception
					}
				} else {
					try {
						intermediateMap.put(key,
								Double.parseDouble(amt.replaceAll(",", "")));
					} catch (Exception e) {
						intermediateMap.put(key, 0.0);

					}
				}

			}
			List<String> allDates = extractDistinctDates(intermediateMap);
			Map<String, List<Double>> currencyMap = extractCurrency(intermediateMap);
			generateCashBalacneReportData(allDates, currencyMap,
					intermediateMap);
			// -----------------------------//
			// Map<String, List<CurrencyAmtValue>> cashBalanceMap =
			// convertToUIModel(intermediateMap);

			CashBalanceReportChartModel chartData = new CashBalanceReportChartModel();

			chartData.setValueDates(allDates);
			chartData
					.setCashBalanceCurrencyAmountModel(CashBalanceCurrencyAmountModel
							.adaptTo(currencyMap));
			return chartData;
		} catch (Exception e) {
			logger.error("Unable to Parse Cash Balances Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching bar chart data for Cash Balances Report ");
		}
	}

	/**
	 * Prepare cash balance report chart data
	 * 
	 * @param allDates
	 * @param currencyMap
	 * @param intermediateMap
	 */
	private void generateCashBalacneReportData(List<String> allDates,
			Map<String, List<Double>> currencyMap,
			Map<DateCurrencyKey, Double> intermediateMap) {
		Iterator<String> itr = allDates.iterator();
		while (itr.hasNext()) {
			String currentDate = itr.next();
			Map<String, Double> tmpMap = new HashMap<String, Double>();
			for (Map.Entry<DateCurrencyKey, Double> entry : intermediateMap
					.entrySet()) {
				if (currentDate.equals(entry.getKey().getDate())) {
					tmpMap.put(entry.getKey().getCurrency(), entry.getValue());
				}
			}
			for (Map.Entry<String, List<Double>> currencyEntry : currencyMap
					.entrySet()) {
				if (tmpMap.containsKey(currencyEntry.getKey())) {
					currencyEntry.getValue().add(
							tmpMap.get(currencyEntry.getKey()));
				} else {
					currencyEntry.getValue().add(0.0);
				}

			}
		}
	}
	
	/**
	 * Parse excel to display cash by clearer details first report data into grid
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getcashbyclearerdetailsfirstgrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	CashByClearerHeaderAndData getCashByClearerDetailsFirstGrid() throws EzopsUIException {
		Report report = null;
		CashByClearerHeaderAndData cashByClearerHeaderAndData = new CashByClearerHeaderAndData();
		try {
			logger.debug("Parsing cash by clearer details first report ");
			String fs = System.getProperty("file.separator");
			report = new Report();
			report.loadExcelCSV(new File(REPORTS_FOLDER_PATH + fs + "CashByClearer.csv"));
			List<CashByClearer> cashByClearerDataList =  new ReportMapper().map(CashByClearer.class, report);
			List<String> columnNameList=Arrays.asList(report.getColumns());
			cashByClearerHeaderAndData.setColumsList(columnNameList);
			cashByClearerHeaderAndData.setReportDataList(cashByClearerDataList);
			return cashByClearerHeaderAndData;
		} catch (Exception e) {
			logger.error("Unable to Parse cash by clearer details first Report "+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException("Error ocurred while " +
					"fetching grid data for cash by clearer details first Report ");
		}
	}
	
	/**
	 * Parse excel to display cash by clearer details second report data into grid
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getcashbyclearerdetailssecondgrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<CashBalancesChartModel> getCashByClearerDetailsSecondGrid() throws EzopsUIException {

		Report report = null;
		// String fs = System.getProperty("file.seperator");
		// List<CashBalancesChartModel> cashBalancesGridList = new
		// ArrayList<CashBalancesChartModel>();
		try {
			logger.debug("Parsing cash by clearer details second report ");
			String fs = System.getProperty("file.separator");
			report = new Report();
			report.loadExcelCSV(new File(REPORTS_FOLDER_PATH + fs + "CashByClearerDetails.csv"));
			return new ReportMapper().map(CashBalancesChartModel.class, report);
		} catch (Exception e) {
			logger.error("Unable to Parse Cash Balances Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for cash by clearer details second Report ");
		}
		// System.out.println(cashBalancesGridList.toString());
		// return cashBalancesGridList;
	}

	/**
	 * Parse cash by clearer report to display data into chart
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getcashbyclearerchart", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<Object> getCashByClearerChart(
			@RequestParam(value = "refresh", required = false, defaultValue = "false") boolean refreshed) throws EzopsUIException {

		Report report = null;
		try {
			checkForRefresh(refreshed, createCashByClearerCriteria());
			logger.debug("Parsing cash by clearer Report ");
			report = new Report();
			String fs = System.getProperty("file.separator");
			report.loadExcelCSV(new File(REPORTS_FOLDER_PATH + fs + "CashByClearer.csv"));
			String[] headerArr = report.getColumns();
			String[] arr = new String[5]; int k=0;
			for(int j=2; j<=6;j++){
				arr[k]=format2.format(format1.parse(headerArr[j]));
				k++;
			}
			Map<String, Map<String, CashByClearerSeriesCurrencyData>> intermediateMap = new LinkedHashMap<>();
			for(int i=2;i<=6;i++){
				Date date = format1.parse(headerArr[i]);
				String dateStr = format2.format(date);
				Iterator<Row> iterator = report.iterator();
				while(iterator.hasNext()){
					Row row = iterator.next();
					if(intermediateMap.containsKey(removeDoubleQuotes(row.getValue(0))) && 
							intermediateMap.get(removeDoubleQuotes(row.getValue(0))).containsKey(removeDoubleQuotes(dateStr))){
				    Map<String, CashByClearerSeriesCurrencyData> oldMap = intermediateMap.get(removeDoubleQuotes(row.getValue(0)));
					CashByClearerSeriesCurrencyData oldObj  = oldMap.get(removeDoubleQuotes(dateStr));
					oldObj.setCurrencyDateAmount(oldObj.getCurrencyDateAmount()+parseDouble(removeDoubleQuotes(row.getValue(i))));
					oldMap.put(removeDoubleQuotes(dateStr), oldObj);
					}
					else if(intermediateMap.containsKey(removeDoubleQuotes(row.getValue(0)))){
						Map<String, CashByClearerSeriesCurrencyData> oldMap = intermediateMap.get(row.getValue(0));
						CashByClearerSeriesCurrencyData obj = new CashByClearerSeriesCurrencyData();
						obj.setCurrencyName(removeDoubleQuotes(dateStr));
						obj.setDrilldownStr(removeDoubleQuotes(dateStr)+removeDoubleQuotes(row.getValue(0)));
						obj.setCurrencyDateAmount(parseDouble((removeDoubleQuotes(row.getValue(i)))));	
						oldMap.put(removeDoubleQuotes(dateStr), obj);
					}
					else{
						CashByClearerSeriesCurrencyData obj = new CashByClearerSeriesCurrencyData();
						obj.setCurrencyName(removeDoubleQuotes(dateStr));
						obj.setDrilldownStr(removeDoubleQuotes(dateStr)+removeDoubleQuotes(row.getValue(0)));
						obj.setCurrencyDateAmount(parseDouble((removeDoubleQuotes(row.getValue(i)).replaceAll(",", ""))));
						Map<String, CashByClearerSeriesCurrencyData> map = new LinkedHashMap<>();
						map.put(removeDoubleQuotes(dateStr), obj);
						intermediateMap.put(removeDoubleQuotes(row.getValue(0)), map);
					}
				}
			}
			
			Map<String, CashByClearerSeriesDrildownData> intermediateMap2 = new LinkedHashMap<>();
			for(int i=2;i<=6;i++){
				Date date = format1.parse(headerArr[i]);
				String dateStr = format2.format(date);
				Iterator<Row> iterator = report.iterator();
				while(iterator.hasNext()){
					Row row = iterator.next();
				    if(intermediateMap2.containsKey(removeDoubleQuotes(dateStr)+removeDoubleQuotes(row.getValue(0)))){
						CashByClearerSeriesDrildownData cashByClearerObj = intermediateMap2.get(removeDoubleQuotes(dateStr)+removeDoubleQuotes(row.getValue(0)));
						List<List<Object>> accountNameList = cashByClearerObj.getGlANameAmount();
						List<Object> list = new LinkedList<>();
						list.add(removeDoubleQuotes((row.getValue(1)== null ? "NA" : row.getValue(1))));
						list.add(parseDouble(removeDoubleQuotes(row.getValue(i))));
						accountNameList.add(list);
					}
					else{
						CashByClearerSeriesDrildownData obj = new CashByClearerSeriesDrildownData();
						obj.setCurrencyName(removeDoubleQuotes(row.getValue(0)));
						obj.setChartType("column");
						obj.setChartDrillDownId(removeDoubleQuotes(removeDoubleQuotes(dateStr)+removeDoubleQuotes(row.getValue(0))));
						List<List<Object>> accountNameList = new ArrayList<>();
						List<Object> list = new LinkedList<>();
						list.add(removeDoubleQuotes((row.getValue(1)== null ? "NA" : row.getValue(1))));
						list.add(parseDouble(removeDoubleQuotes(row.getValue(i))));
						accountNameList.add(list);
						obj.setGlANameAmount(accountNameList);
						intermediateMap2.put(removeDoubleQuotes(dateStr)+removeDoubleQuotes(row.getValue(0)), obj);
					}
				}
			}
			List<Object> resultDataList = new ArrayList<>();
			resultDataList= CashClearerResultData.adaptToData(intermediateMap,intermediateMap2);
			resultDataList.add(arr);
			return resultDataList;
		} catch (Exception e) {
			logger.error("Unable to Parse Cash By Clearer Report "+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching bar chart data for Cash By Clearer Report ");
		}
	}
	
	/**
	 * Extract currency
	 * 
	 * @param intermediateMap
	 * @return
	 */
	private Map<String, List<Double>> extractCurrency(
			Map<DateCurrencyKey, Double> intermediateMap) {
		List<String> currency = new ArrayList<String>();
		for (Map.Entry<DateCurrencyKey, Double> entry : intermediateMap
				.entrySet()) {
			currency.add(entry.getKey().getCurrency());
		}
		Set<String> currencySet = new HashSet<String>(currency);
		if (!currency.isEmpty()) {
			currency.clear();
		}
		currency.addAll(currencySet);
		Map<String, List<Double>> currencyMap = new HashMap<String, List<Double>>();
		Iterator<String> itr = currency.iterator();
		while (itr.hasNext()) {
			String curencyNextVal = itr.next();
			List<Double> amt = new ArrayList<Double>();
			currencyMap.put(curencyNextVal, amt);
		}
		return currencyMap;
	}

	/**
	 * Extract distinct dates
	 * 
	 * @param intermediateMap
	 * @return
	 */
	private List<String> extractDistinctDates(
			Map<DateCurrencyKey, Double> intermediateMap) {
		List<String> allDates = new ArrayList<String>();
		for (Map.Entry<DateCurrencyKey, Double> entry : intermediateMap
				.entrySet()) {
			allDates.add(entry.getKey().getDate());
		}
		Set<String> datesSet = new HashSet<String>(allDates);
		if (!allDates.isEmpty()) {
			allDates.clear();
		}
		allDates.addAll(datesSet);
		return allDates;
	}

	/**
	 * For grouping data and currency
	 * 
	 * @author dinesh
	 * 
	 */
	static class DateCurrencyKey {

		public DateCurrencyKey() {

		}

		public DateCurrencyKey(String currency, String date) {
			super();
			this.currency = currency;
			this.date = date;
		}

		@Override
		public String toString() {
			return "DateCurrencyKey [currency=" + currency + ", date=" + date
					+ "]";
		}

		String currency;
		String date;

		@Override
		public int hashCode() {
			final int prime = 31;
			int result = 1;
			result = prime * result
					+ ((currency == null) ? 0 : currency.hashCode());
			result = prime * result + ((date == null) ? 0 : date.hashCode());
			return result;
		}

		@Override
		public boolean equals(Object obj) {
			if (this == obj)
				return true;
			if (obj == null)
				return false;
			if (getClass() != obj.getClass())
				return false;
			DateCurrencyKey other = (DateCurrencyKey) obj;
			if (currency == null) {
				if (other.currency != null)
					return false;
			} else if (!currency.equals(other.currency))
				return false;
			if (date == null) {
				if (other.date != null)
					return false;
			} else if (!date.equals(other.date))
				return false;
			return true;
		}

		public String getDate() {
			return date;
		}

		public void setDate(String date) {
			this.date = date;
		}

		public String getCurrency() {
			return currency;
		}

		public void setCurrency(String currency) {
			this.currency = currency;
		}

	}

	/**
	 * Parse excel to display cash upcoming pay receive report data into grid
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getcashupcomingpayreceivegrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<CashBalancesChartModel> getCashUpcomingPayReceiveGrid()
			throws EzopsUIException {

		Report report = null;
		// String fs = System.getProperty("file.seperator");
		// List<CashBalancesChartModel> cashUpcomingPayReceiveGridList = new
		// ArrayList<CashBalancesChartModel>();
		try {
			logger.debug("Parsing Cash Balances Report ");
			String fs = System.getProperty("file.separator");
			report = new Report();
			report.loadExcelCSV(new File(REPORTS_FOLDER_PATH + fs + "CashUpcoming.csv"));
			return new ReportMapper().map(CashBalancesChartModel.class, report);
		} catch (Exception e) {
			logger.error("Unable to Parse Cash Upcoming Pay Receive Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for Cash Upcoming Pay Receive Report ");
		}
		// System.out.println(cashUpcomingPayReceiveGridList.toString());
		// return cashUpcomingPayReceiveGridList;
	}

	/**
	 * Parse cash balance report to display data into chart
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getcashupcomingpayrecievechart", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<CashUpcomingPayRecieveModel> getCashUpcomingPayRecieveChart()
			throws EzopsUIException {

		Report report = null;
		String fs = System.getProperty("file.separator");
		Map<String, Double> responseMesage = new HashMap<String, Double>();
		List<CashUpcomingPayRecieveModel> cashUpcomingPayRecieveList = new ArrayList<CashUpcomingPayRecieveModel>();
		try (FileInputStream fis = new FileInputStream(new File(
				REPORTS_FOLDER_PATH) + fs + "CashUpcoming.csv")) {
			report = new Report();
			report.loadExcelCSV(new File(REPORTS_FOLDER_PATH + fs + "CashUpcoming.csv"));
			Iterator<Row> iterator = report.iterator();
			while (iterator.hasNext()) {
				Row row = iterator.next();
				String valueDate = removeDoubleQuotes(row.getValue(0));
				format1.setLenient(true);
				Date date = format1.parse(valueDate);
				String dateStr = format2.format(date);
				String cashAmount = removeDoubleQuotes(row.getValue(6));

				if (responseMesage.containsKey(dateStr)) {
					Double oldValue = responseMesage.get(dateStr);
					try {
						responseMesage.put(
								dateStr,
								oldValue
										+ Double.parseDouble(cashAmount
												.replaceAll(",", "")));
					} catch (Exception e) {
						// eat the exception
						e.printStackTrace();
					}
				} else {
					try {
						responseMesage.put(dateStr, Double
								.parseDouble(cashAmount.replaceAll(",", "")));
					} catch (Exception e) {
						e.printStackTrace();
						responseMesage.put(dateStr, 0.0);
					}
				}
			}
			/*
			 * responseMesage = ReportDataHelper
			 * .convertMapValuesInPercentage(responseMesage);
			 */
			cashUpcomingPayRecieveList = ReportDataHelper
					.convertMapToList1(responseMesage);

		} catch (Exception e) {
			logger.error("Unable to Parse Cash Balances Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching bar chart data for Cash Balances Report ");
		}
		return cashUpcomingPayRecieveList;
	}

	/**
	 * Parse excel to display risk cash flow report data into grid
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getriskcashflowgrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<RiskCashFlowAnalysis> getRiskCashFlowGrid() throws EzopsUIException {

		Report report = null;
		// List<RiskCashFlowAnalysis> riskCashFlowAnalysisGridList = new
		// ArrayList<RiskCashFlowAnalysis>();
		try {
			logger.debug("Parsing Cash Balances Report ");
			String fs = System.getProperty("file.separator");
			report = new Report();
			report.loadXls(new FileInputStream(new File(REPORTS_FOLDER_PATH)
					+ fs + "Risk_CashFlow Analysis_SS.xlsx"), "Sheet");
			return new ReportMapper().map(RiskCashFlowAnalysis.class, report);
		} catch (Exception e) {
			logger.error("Unable to Parse Risk Cash Flow Analysis Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for Risk Cash Flow Analysis Report ");
		}
	}

	/**
	 * Parse excel to display risk cash flow report data into grid
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getrisktimehorizongrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<RiskTimeHorizonModel> getRiskTimeHorizonGrid() throws EzopsUIException {

		Report report = null;
		// List<RiskTimeHorizonModel> riskTimeHorizonModelGridList = new
		// ArrayList<RiskTimeHorizonModel>();
		try {
			logger.debug("Parsing Cash Balances Report ");
			String fs = System.getProperty("file.separator");
			report = new Report();
			report.loadXls(new FileInputStream(new File(REPORTS_FOLDER_PATH)
					+ fs + "Risk_Time Horizon_SS.xlsx"), "Sheet");
			return new ReportMapper().map(RiskTimeHorizonModel.class, report);
		} catch (Exception e) {
			logger.error("Unable to Parse Risk Time Horizon Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for Risk Time Horizon Report ");
		}
	}

	/**
	 * Parse excel to display Final Depot Projection report data into grid
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getfinaldepotprojectiongrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<FinalDepotProjectionModel> getFinalDepotProjectionGrid()
			throws EzopsUIException {

		Report report = null;
		// List<FinalDepotProjectionModel> finalDepotProjectionModelList = new
		// ArrayList<FinalDepotProjectionModel>();
		try {
			logger.debug("Parsing Cash Balances Report ");
			String fs = System.getProperty("file.separator");
			report = new Report();
			report.loadXls(new FileInputStream(new File(REPORTS_FOLDER_PATH)
					+ fs + "Final_Depot_Projection_SS.xlsx"),
					"Unaggregated Depot Projections");
			return new ReportMapper().map(FinalDepotProjectionModel.class,
					report);
		} catch (Exception e) {
			logger.error("Unable to Parse Final Depot Projection Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for Final Depot Projection Report ");
		}
	}

	/**
	 * Parse csv to display master aggrement final report data into grid
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getmasteraggrementfinalgrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<MasterAgreementFinalModel> getMasterAgreementFinalGrid(
			@RequestParam(value = "refresh", required = false, defaultValue = "false") boolean refreshed)
			throws EzopsUIException {

		Report report = null;
		// List<MasterAgreementFinalModel> masterAggrementFinalModelModelList =
		// new ArrayList<MasterAgreementFinalModel>();
		try {
			checkForRefresh(refreshed, createMasterAggrementCriteria());
			logger.debug("Parsing Cash Balances Report ");
			report = new Report();
			report.load(new File(reportLocator
					.locate(createMasterAggrementCriteria())));
			return new ReportMapper().map(MasterAgreementFinalModel.class,
					report);
		} catch (Exception e) {
			logger.error("Unable to Parse Master Aggrement Final Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for Master Aggrement Final Report ");
		}
	}

	private ReportCriteria createMasterAggrementCriteria() {
		return ReportCriteriaImpl
				.createInstance()
				.addFilter(Filter.REPORT_NAME,
						EzopsReportsProvider.MASTER_AGGREMENT.getFileName())
				.addFilter(Filter.REPORT_DISPLAY_NAME,
						EzopsReportsProvider.MASTER_AGGREMENT.getDisplayName());
	}

	private ReportCriteria createLifeCycleEventCriteria() {
		return ReportCriteriaImpl
				.createInstance()
				.addFilter(Filter.REPORT_NAME,
						EzopsReportsProvider.LIFE_CYCLE_EVENT.getFileName())
				.addFilter(Filter.REPORT_DISPLAY_NAME,
						EzopsReportsProvider.LIFE_CYCLE_EVENT.getDisplayName());
	}
	
	private ReportCriteria createCashByClearerCriteria() {
		return ReportCriteriaImpl
				.createInstance()
				.addFilter(Filter.REPORT_NAME,
						EzopsReportsProvider.CASH_BY_CLEARER.getFileName())
				.addFilter(Filter.REPORT_DISPLAY_NAME,
						EzopsReportsProvider.CASH_BY_CLEARER.getDisplayName());
	}

	/**
	 * Parse Master Agreement Pie Chart
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getmasteragreemengpiechart", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<MasterAgreementChartModel> getMasterAgreemengPieChart(
			@RequestParam(value = "refresh", required = false, defaultValue = "false") boolean refreshed)
			throws EzopsUIException {

		checkForRefresh(refreshed, createMasterAggrementCriteria());
		Report report = null;
		Map<String, Integer> responseMesage = new HashMap<String, Integer>();
		try {
			report = new Report();
			report.load(new File(reportLocator
					.locate(createMasterAggrementCriteria())));
			Iterator<Row> iterator = report.iterator();
			while (iterator.hasNext()) {
				Row row = iterator.next();
				if (responseMesage.containsKey(row.getValue(4))) {
					responseMesage.put(row.getValue(4),
							responseMesage.get(row.getValue(4)) + 1);

				} else {
					responseMesage.put(row.getValue(4), 1);
				}
			}
			List<MasterAgreementChartModel> chartList = MasterAgreementChartModel
					.adaptTo(responseMesage);
			return chartList;
		} catch (Exception e) {
			logger.error("Unable to Parse Position By CounterParty CSV "
					+ e.getMessage());
			throw new EzopsUIException(
					"Error ocurred while showing position by currency chart");
		}
	}

	/**
	 * Parse Master Agreement bar Chart
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getmasteragreementbarchart", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	MasterAggrementReportChartModel getMasterAgreementBarChart(
			@RequestParam(value = "refresh", required = false, defaultValue = "false") boolean refreshed)
			throws EzopsUIException {

		checkForRefresh(refreshed, createMasterAggrementCriteria());
		Report report = null;
		Map<String, List<Integer>> responseMessage = new HashMap<String, List<Integer>>();
		try {
			report = new Report();
			report.load(new File(reportLocator
					.locate(createMasterAggrementCriteria())));
			Iterator<Row> iterator = report.iterator();
			List<String> partyIdList = new ArrayList<String>();
			List<String> agreementTypeList = new ArrayList<String>();
			while (iterator.hasNext()) {
				Row row = iterator.next();
				partyIdList.add(row.getValue(4));
				agreementTypeList.add(row.getValue(2));
			}

			responseMessage = ReportDataHelper.getMasterAggrementMapData(
					partyIdList, agreementTypeList);
			MasterAggrementReportChartModel chartData = new MasterAggrementReportChartModel();
			chartData.setPartyIdList(partyIdList);
			chartData
					.setMasterAggrementAgreementCountModelList(MasterAggrementAgreementCountModel
							.adaptTo(responseMessage));
			return chartData;
		} catch (Exception e) {
			logger.error("Unable to Parse Position By CounterParty CSV "
					+ e.getMessage());
			throw new EzopsUIException(
					"Error ocurred while showing position by currency chart");
		}
	}

	/**
	 * Parse cash balance report to display data into chart
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getriskcashflowanalysischart", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	CashBalanceReportChartModel getRiskCashFlowAnalysisChart()
			throws EzopsUIException {

		Report report = null;
		// Map<String, Double> responseMesage = new HashMap<String, Double>();
		// List<CashUpcomingPayRecieveModel> cashUpcomingPayRecieveList = new
		// ArrayList<CashUpcomingPayRecieveModel>();
		try {
			logger.debug("Parsing Cash Balances Report ");
			report = new Report();
			String fs = System.getProperty("file.separator");
			report.loadXls(new FileInputStream(new File(REPORTS_FOLDER_PATH)
					+ fs + "Risk_CashFlow Analysis_SS.xlsx"), "Sheet");
			Iterator<Row> iterator = report.iterator();

			Map<DateCurrencyKey, Double> intermediateMap = new HashMap<>();
			while (iterator.hasNext()) {
				Row row = iterator.next();
				String currency = row.getValue(13);
				String date = row.getValue(0);
				Date date1 = format.parse(date);
				String dateStr = format2.format(date1);
				String amt = row.getValue(1);

				DateCurrencyKey key = new DateCurrencyKey(currency, dateStr);

				if (intermediateMap.containsKey(key)) {
					Double oldValue = intermediateMap.get(key);
					try {
						intermediateMap.put(key,
								oldValue + Double.parseDouble(amt));
					} catch (Exception e) {
						// eat the exception
					}
				} else {
					try {
						intermediateMap.put(key,
								Double.parseDouble(amt.replaceAll(",", "")));
					} catch (Exception e) {
						intermediateMap.put(key, 0.0);

					}
				}

			}

			List<String> allDates = extractDistinctDates(intermediateMap);
			Map<String, List<Double>> currencyMap = extractCurrency(intermediateMap);
			generateCashBalacneReportData(allDates, currencyMap,
					intermediateMap);
			// -----------------------------//
			// Map<String, List<CurrencyAmtValue>> cashBalanceMap =
			// convertToUIModel(intermediateMap);

			CashBalanceReportChartModel chartData = new CashBalanceReportChartModel();

			chartData.setValueDates(allDates);
			chartData
					.setCashBalanceCurrencyAmountModel(CashBalanceCurrencyAmountModel
							.adaptTo(currencyMap));
			return chartData;
		} catch (Exception e) {
			logger.error("Unable to Parse Cash Balances Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching bar chart data for Cash Balances Report ");
		}
	}

	/**
	 * Parse CSV to display Open Trades grid on Life Cycle Event page
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getopentradesgrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<LifeCycleEventModel> getOpenTradesgrid(
			@RequestParam(value = "refresh", required = false, defaultValue = "false") boolean refreshed,
			@RequestParam(value = "fromDate", required = false, defaultValue = "") String fromDate,
			@RequestParam(value = "toDate", required = false, defaultValue = "") String toDate)
			throws EzopsUIException {

		Report report = null;
		List<LifeCycleEventModel> lifeCycleEventModelList = new ArrayList<>();
		try {
			checkForRefresh(refreshed, createLifeCycleEventCriteria());
			logger.debug("Parsing Life Cycle Event Report ");
			report = new Report();
			report.load(new File(reportLocator
					.locate(createLifeCycleEventCriteria())));
			lifeCycleEventModelList = new ReportMapper().map(
					LifeCycleEventModel.class, report);
			Iterator<LifeCycleEventModel> iterator = lifeCycleEventModelList
					.iterator();
			while (iterator.hasNext()) {
				LifeCycleEventModel lifeCycleEventModel = iterator.next();
				String settlementDate = lifeCycleEventModel.getSettlementDate();
				if (!fromDate.trim().equals("") && !toDate.trim().equals("")) {
					if (settlementDate != null
							&& !dateFilterCheck(fromDate, toDate,
									settlementDate)) {
						iterator.remove();
					}
				}
			}
			return lifeCycleEventModelList;
		} catch (Exception e) {
			logger.error("Unable to Parse Life Cycle Event Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for Life Cycle Event Report ");
		}
	}

	private boolean dateFilterCheck(String fromDate, String toDate,
			String settlementDate) throws ParseException {

		Date fromDates = format1.parse(fromDate);
		Date toDates = format1.parse(toDate);
		Date settlemntDate = format.parse(settlementDate);
		int fromDateCheck = fromDates.compareTo(settlemntDate);
		int toDateCheck = toDates.compareTo(settlemntDate);
		if (fromDateCheck <= 0 && toDateCheck >= 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * Parse CSV to display Open Trades grid on Life Cycle Event page
	 * 
	 * @return
	 * @throws EzopsUIException
	 */
	@RequestMapping(value = "/getupcomingsettlementgrid", method = RequestMethod.GET, produces = "application/json")
	public @ResponseBody
	List<LifeCycleEventModel> getUpcomingSettlementGrid(
			@RequestParam(value = "refresh", required = false, defaultValue = "false") boolean refreshed,
			@RequestParam(value = "fromDate", required = false, defaultValue = "") String fromDate,
			@RequestParam(value = "toDate", required = false, defaultValue = "") String toDate)
			throws EzopsUIException {

		Report report = null;
		List<LifeCycleEventModel> upcomingSettlementList = new ArrayList<>();
		try {
			checkForRefresh(refreshed, createLifeCycleEventCriteria());
			logger.debug("Parsing Life Cycle Event Report ");
			report = new Report();
			report.load(new File(reportLocator
					.locate(createLifeCycleEventCriteria())));
			upcomingSettlementList = new ReportMapper().map(
					LifeCycleEventModel.class, report);
			Iterator<LifeCycleEventModel> iterator = upcomingSettlementList
					.iterator();
			while (iterator.hasNext()) {
				LifeCycleEventModel lifeCycleEventModel = iterator.next();
				Date settlementDt = format.parse(lifeCycleEventModel
						.getSettlementDate());
				String settlementDate = lifeCycleEventModel.getSettlementDate();
				if (!fromDate.trim().equals("") && !toDate.trim().equals("")) {
					if (settlementDt != null
							&& (settlementDt.compareTo(new Date())) >= 0
							&& !dateFilterCheck(fromDate, toDate,
									settlementDate)) {
						iterator.remove();
					}
				} else if (settlementDt != null
						&& !(settlementDt.compareTo(new Date()) >= 0)) {
					iterator.remove();
				}
			}

			return upcomingSettlementList;
		} catch (Exception e) {
			logger.error("Unable to Parse Life Cycle Event Upcomming Settlement Report "
					+ e.getMessage());
			e.printStackTrace();
			throw new EzopsUIException(
					"Error ocurred while fetching grid data for Life Cycle Event Upcomming Settlement Report ");
		}
	}

	
	private String getValue(String value) {
		return value == null ? "" : value;
	}

	private String removeDoubleQuotes(String value) {
		String checkValue = getValue(value);
		return checkValue.replaceAll("^\"|\"$", "");
	}
	
	public static void main(String[] args) throws IOException {
		Reader in = new FileReader("C:\\Users\\dinesh\\EZOPS\\Reports\\CashUpcoming.csv");
		Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(in);
		Iterator<CSVRecord> itr  = records.iterator();
		while(itr.hasNext()){
			//CSVRecord csv = itr.next();
		}
	}

}
